<?php
class APaymentConfirm extends CActiveRecord
{
    public function getPaymentConfirm($search, $currentPage, $numberRecordPerPage)
    {
        $startRecord = ($currentPage - 1) * $numberRecordPerPage;
        
        $condition = "";
        $condition .= " AND month=" . $search["month"];
        $condition .= " AND year=" . $search["year"];
        $condition .= !empty($search["type"]) ? " AND type=" . $search["type"] : "";
        if(!empty($search["username"])){
            $sql = "SELECT id FROM c_user WHERE username='" . mysql_escape_string($search["username"]) . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();
            $condition .= empty($rows) ? " AND userId=0" : " AND userId=" . $rows["id"];
        }
        
        $sql = "SELECT * FROM c_payment_confirm WHERE 1 " . $condition . " ORDER BY create_date LIMIT " . $startRecord . ", " . $numberRecordPerPage;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        
        $list_user_id = "0";
        foreach($rows as $row){
            $list_user_id .= "," . $row["userId"];
        }
        
        $sql = "SELECT id, username FROM c_user WHERE id IN (" . $list_user_id . ")";
        $command = Yii::app()->db->createCommand($sql);
        $rowsUser = $command->queryAll();
        $replaceUser = array();
        
        foreach($rowsUser as $row){
            $replaceUser[$row["id"]] = $row["username"];  
        }
        
        $i = 0;
        while(isset($rows[$i])){
            $rows[$i]["username"] = isset($replaceUser[$rows[$i]["userId"]]) ? $replaceUser[$rows[$i]["userId"]] : "";
            $i++;
        }
        
        return $rows;
    }
    
    public function getTotalPaymentConfirm($search)
    {
        $condition = "";
        $condition .= " AND month=" . $search["month"];
        $condition .= " AND year=" . $search["year"];
        $condition .= !empty($search["type"]) ? " AND type=" . $search["type"] : "";
        if(!empty($search["username"])){
            $sql = "SELECT id FROM c_user WHERE username='" . mysql_escape_string($search["username"]) . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();
            $condition .= empty($rows) ? " AND userId=0" : " AND userId=" . $rows["id"];
        }
        
        $sql = "SELECT count(id) FROM c_payment_confirm WHERE 1 " . $condition ;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();
        return $rows["count(id)"];
    }
    
    public function getPaymentConfirmExport($search)
    {
        $condition = "";
        $condition .= " AND month=" . $search["month"];
        $condition .= " AND year=" . $search["year"];
        $condition .= !empty($search["type"]) ? " AND type=" . $search["type"] : "";
        if(!empty($search["username"])){
            $sql = "SELECT id FROM c_user WHERE username='" . mysql_escape_string($search["username"]) . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();
            $condition .= empty($rows) ? " AND userId=0" : " AND userId=" . $rows["id"];
        }
        
        $sql = "SELECT * FROM c_payment_confirm WHERE 1 " . $condition . " ORDER BY create_date";
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        
        $list_user_id = "0";
        foreach($rows as $row){
            $list_user_id .= "," . $row["userId"];
        }
        
        $sql = "SELECT id, username FROM c_user WHERE id IN (" . $list_user_id . ")";
        $command = Yii::app()->db->createCommand($sql);
        $rowsUser = $command->queryAll();
        $replaceUser = array();
        
        foreach($rowsUser as $row){
            $replaceUser[$row["id"]] = $row["username"];  
        }
        
        $i = 0;
        while(isset($rows[$i])){
            $rows[$i]["username"] = isset($replaceUser[$rows[$i]["userId"]]) ? $replaceUser[$rows[$i]["userId"]] : "";
            $i++;
        }
        
        return $rows;
    }        
    
    public function getPaymentInfo($id)
    {
        $sql = "SELECT * FROM c_payment_confirm WHERE id=" . $id;        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();
        return $rows;    
    }
}  
?>
